import mysql.connector
from mysql.connector import Error
import os
from dotenv import load_dotenv

# 加载环境变量（安全存储数据库凭据）
load_dotenv()


def get_fail_reason(order_response):
    """查询并返回所有产品ID"""
    connection = None
    try:

        connection = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'rm-uf606049v7j2kbbv35o.mysql.rds.aliyuncs.com'),
            user=os.getenv('DB_USER', 'pay'),
            password=os.getenv('DB_PASSWORD', '!@ChMwem6GdiJkq3DN'),
            database=os.getenv('DB_NAME', 'pay'),
            port=os.getenv('DB_PORT', 3109)
        )

        if connection.is_connected():
            print("成功连接到MySQL数据库!")

            # 创建游标
            cursor1 = connection.cursor()
            order = order_response
            # 执行查询
            query1 = f'''SELECT s.msg AS upstream_error_msg
                        FROM pay_payout_order o
                        LEFT JOIN pay_payout_source_order s ON s.order_id = o.id
                        WHERE o.order_sn = '{order}'
                          or o.trade_sn = '{order}'
                          AND o.status = 5
                          AND s.id = (
                              SELECT MAX(id)
                              FROM pay_payout_source_order
                              WHERE order_id = o.id  
                          );
                    '''

            cursor1.execute(query1)

            # 获取所有数据
            results1 = cursor1.fetchall()
            if len(results1) > 0:
                return results1[0][0]
            else:
                return results1


    except Error as e:
        print(f"数据库错误: {e}")
        return []

    finally:
        # 确保关闭连接
        if connection and connection.is_connected():
            cursor1.close()
            connection.close()
            print("数据库连接已关闭")
